using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class Pages_Raw_Store_Card : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

        
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        string item_opening_query = "select itype,icode,op_qty as qty,op_rate as rate from stlecard where month=" + DropDownListMonth.SelectedValue + " and year=" + DropDownListYear.SelectedValue + " and op_qty>0";
        string item_load_query = "select itype,icode,iname from stock where exists (select descript,code from type where varfield='ITYPE' and type='RA' and stock.itype = type.code)order by stock.iname";
        string item_receipt_query = "select itype,icode,sum(qty) as qty,sum(w_ton) as ton ,sum(W_ton*cost) as amount from memo where nature='SRM' and month(date)=" + DropDownListMonth.SelectedValue + " and year(date)=" + DropDownListYear.SelectedValue + " group by itype,icode order by itype";
        string item_consumption_query = "select itype,icode,sum(qty) as qty,sum(w_ton) as ton  from memo where nature='SDM' and month(date)=" + DropDownListMonth.SelectedValue + " and year(date)=" + DropDownListYear.SelectedValue + " group by itype,icode order by itype";
        DataSet ds = new DataSet();
        Common.Getdata(ds, item_opening_query, "Stkopening");
        Common.Getdata(ds, item_load_query, "Stkname");
        Common.Getdata(ds, item_receipt_query, "Stkreceipt");
        Common.Getdata(ds, item_consumption_query, "Stkconsumption");


        DataTable tb_storeledgercard;
        tb_storeledgercard = ds.Tables["Stkreceipt"].Clone();

        tb_storeledgercard.Columns.Remove("qty");
        tb_storeledgercard.Columns.Remove("ton");
        tb_storeledgercard.Columns.Remove("amount");


        tb_storeledgercard.Columns.Add("iname", typeof(string));

        tb_storeledgercard.Columns.Add("op_qty", typeof(float));
        tb_storeledgercard.Columns.Add("op_rate", typeof(float));
        tb_storeledgercard.Columns.Add("op_amount", typeof(float));

        tb_storeledgercard.Columns.Add("rec_qty", typeof(float));
        tb_storeledgercard.Columns.Add("rec_rate", typeof(float));
        tb_storeledgercard.Columns.Add("rec_amount", typeof(float));

        tb_storeledgercard.Columns.Add("total_qty", typeof(float));
        tb_storeledgercard.Columns.Add("total_rate", typeof(float));
        tb_storeledgercard.Columns.Add("total_amount", typeof(float));

        tb_storeledgercard.Columns.Add("con_qty", typeof(float));
        tb_storeledgercard.Columns.Add("con_rate", typeof(float));
        tb_storeledgercard.Columns.Add("con_amount", typeof(float));

        tb_storeledgercard.Columns.Add("close_qty", typeof(float));
        tb_storeledgercard.Columns.Add("close_rate", typeof(float));
        tb_storeledgercard.Columns.Add("close_amount", typeof(float));


        foreach (DataRow arow in ds.Tables["Stkname"].Rows)
        {
            DataRow dr = tb_storeledgercard.NewRow();
            dr["itype"] = arow["itype"];
            dr["icode"] = arow["icode"];
            dr["iname"] = arow["iname"];
            tb_storeledgercard.Rows.Add(dr);
        }
        // collect opening data
        string searchexpression3;
        DataRow[] foundrows3;
        int k;
        for (k = 0; k < tb_storeledgercard.Rows.Count; k++)
        {
            searchexpression3 = "itype=" + tb_storeledgercard.Rows[k]["itype"] + "and icode=" + tb_storeledgercard.Rows[k]["icode"];
            foundrows3 = ds.Tables["Stkopening"].Select(searchexpression3);
            if (foundrows3.Length > 0)
            {
                tb_storeledgercard.Rows[k]["op_qty"] = foundrows3[0]["qty"];
                tb_storeledgercard.Rows[k]["op_rate"] = foundrows3[0]["rate"];
                tb_storeledgercard.Rows[k]["op_amount"] = Convert.ToDecimal(tb_storeledgercard.Rows[k]["op_qty"]) * Convert.ToDecimal(tb_storeledgercard.Rows[k]["op_rate"]);
            }
            else
            {
                tb_storeledgercard.Rows[k]["op_qty"] = 0;
                tb_storeledgercard.Rows[k]["op_rate"] = 0;
                tb_storeledgercard.Rows[k]["op_amount"] = 0;
            }
        }

        // collect receipts data
        string searchexpression;
        DataRow[] foundrows;
        int i;
        for (i = 0; i < tb_storeledgercard.Rows.Count; i++)
        {
            searchexpression = "itype=" + tb_storeledgercard.Rows[i]["itype"] + "and icode=" + tb_storeledgercard.Rows[i]["icode"];
            foundrows = ds.Tables["Stkreceipt"].Select(searchexpression);
            if (foundrows.Length > 0)
            {
                tb_storeledgercard.Rows[i]["rec_qty"] = foundrows[0]["ton"];
                tb_storeledgercard.Rows[i]["rec_amount"] = foundrows[0]["amount"];
                if (Convert.ToDecimal(tb_storeledgercard.Rows[i]["rec_qty"]) > 0)
                    tb_storeledgercard.Rows[i]["rec_rate"] = Convert.ToDecimal(tb_storeledgercard.Rows[i]["rec_amount"]) / Convert.ToDecimal(tb_storeledgercard.Rows[i]["rec_qty"]);
                else
                    tb_storeledgercard.Rows[i]["rec_rate"] = 0;
            }
            else
            {
                tb_storeledgercard.Rows[i]["rec_qty"] = 0;
                tb_storeledgercard.Rows[i]["rec_amount"] = 0;
                tb_storeledgercard.Rows[i]["rec_rate"] = 0;
            }
        }


        // calculate total data
        int l;
        for (l = 0; l < tb_storeledgercard.Rows.Count; l++)
        {
            tb_storeledgercard.Rows[l]["total_qty"] = Convert.ToDecimal(tb_storeledgercard.Rows[l]["op_qty"]) + Convert.ToDecimal(tb_storeledgercard.Rows[l]["rec_qty"]);
            tb_storeledgercard.Rows[l]["total_amount"] = Convert.ToDecimal(tb_storeledgercard.Rows[l]["op_amount"]) + Convert.ToDecimal(tb_storeledgercard.Rows[l]["rec_amount"]); ;
            if (Convert.ToInt32(tb_storeledgercard.Rows[l]["total_qty"]) > 0)
                tb_storeledgercard.Rows[l]["total_rate"] = Convert.ToDecimal(tb_storeledgercard.Rows[l]["total_amount"]) / Convert.ToDecimal(tb_storeledgercard.Rows[l]["total_qty"]);
        }


        // collect consumption data
        string searchexpression2;
        DataRow[] foundrows2;
        int j;
        for (j = 0; j < tb_storeledgercard.Rows.Count; j++)
        {
            searchexpression2 = "itype=" + tb_storeledgercard.Rows[j]["itype"] + "and icode=" + tb_storeledgercard.Rows[j]["icode"];
            foundrows2 = ds.Tables["Stkconsumption"].Select(searchexpression2);
            if (foundrows2.Length > 0)
            {
                tb_storeledgercard.Rows[j]["con_qty"] = foundrows2[0]["ton"];
            }
            else
                tb_storeledgercard.Rows[j]["con_qty"] = 0;
        }

        GridView1.DataSource = tb_storeledgercard;
        GridView1.DataBind();
    }
}
